package com.it2297.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import com.it2297.main.App;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

public class Bid
{
	private Timestamp dateBid;
	private Timestamp dateWon;
	private String comment;
	private Needy needy;
	private UsedItem usedItem;

	public Timestamp getDateBid()
	{
		return dateBid;
	}

	public void setDateBid(Timestamp dateBid)
	{
		this.dateBid = dateBid;
	}

	public Timestamp getDateWon()
	{
		return dateWon;
	}

	public void setDateWon(Timestamp dateWon)
	{
		this.dateWon = dateWon;
	}

	public String getComment()
	{
		return comment;
	}

	public void setComment(String comment)
	{
		this.comment = comment;
	}

	public Needy getNeedy()
	{
		return needy;
	}

	public void setNeedy(Needy needy)
	{
		this.needy = needy;
	}

	public UsedItem getUsedItem()
	{
		return usedItem;
	}

	public void setUsedItem(UsedItem usedItem)
	{
		this.usedItem = usedItem;
	}

	public Bid getSingle(String needyNRIC, int usedItemId)
	{
		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.bid WHERE fk_needy_nric = ? AND fk_used_item_id = ?");
			preparedStatement.setString(1, needyNRIC);
			preparedStatement.setInt(2, usedItemId);

			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				this.setAttributes(resultSet);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return this;
	}

	public static ArrayList<Bid> getMultiple()
	{
		ArrayList<Bid> bids = new ArrayList<Bid>();

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.bid");

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				Bid bid = new Bid();
				bid.setAttributes(resultSet);
				bids.add(bid);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return bids;
	}

	public static ArrayList<Bid> getMultiple(int usedItemId)
	{
		ArrayList<Bid> bids = new ArrayList<Bid>();

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT b.*, (n.household_income / n.family_size) AS `MPCI` FROM `americano`.`bid` b INNER JOIN `americano`.`needy` n ON b.fk_needy_nric = n.nric WHERE fk_used_item_id = ? ORDER BY MPCI");
			preparedStatement.setInt(1, usedItemId);
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				Bid bid = new Bid();
				bid.setAttributes(resultSet);
				bids.add(bid);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return bids;
	}

	public static ArrayList<Needy> getBidders(int usedItemId)
	{
		ArrayList<Needy> bidders = new ArrayList<Needy>();

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT fk_needy_nric FROM americano.bid WHERE fk_used_item_id = ?");
			preparedStatement.setInt(1, usedItemId);

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				Needy bidder = new Needy();
				bidder.getSingle(resultSet.getString("fk_needy_nric"));
				bidders.add(bidder);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return bidders;
	}

	public static int getTotalBids()
	{
		int totalBids = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(*) FROM americano.bid");

			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				totalBids = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return totalBids;
	}

	private void setAttributes(ResultSet resultSet)
	{
		try
		{
			this.setComment(resultSet.getString("comment"));
			this.setDateBid(resultSet.getTimestamp("date_bid"));
			this.setDateWon(resultSet.getTimestamp("date_won"));

			// make use of getSingle() method from usedItem object (Note that in
			// UsedItem.java, getSingle() method return itself)
			// return a populated object to this class as an attribute
			// finally, set the usedItem as the returned object
			this.setUsedItem(new UsedItem().getSingle(resultSet.getInt("fk_used_item_id")));

			Needy needy = new Needy();
			needy.getSingle(resultSet.getString("fk_needy_nric"));
			this.setNeedy(needy);

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
	}

	public boolean create()
	{
		int rowAffected = 0;
		boolean isSuccessful = false;
		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("INSERT INTO americano.bid (date_bid, comment, fk_needy_nric, fk_used_item_id) VALUES (?,?,?,?)");
			preparedStatement.setTimestamp(1, dateBid);
			preparedStatement.setString(2, comment);
			preparedStatement.setString(3, needy.getNric());
			preparedStatement.setInt(4, usedItem.getId());

			rowAffected = preparedStatement.executeUpdate();

			if (rowAffected > 0)
			{
				isSuccessful = true;
			}
			else
			{
				isSuccessful = false;
			}
		}
		catch (MySQLIntegrityConstraintViolationException ex)
		{
			ex.printStackTrace();
			isSuccessful = false;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			isSuccessful = false;
		}
		finally
		{
			App.mySqlConnection.close();
		}
		return isSuccessful;
	}

	public static int getWonBidNum(String nric)
	{
		int wonBidNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(DISTINCT fk_used_item_id) AS num_of_won_bid FROM americano.bid WHERE date_won IS NOT NULL AND fk_needy_nric = ?");
			preparedStatement.setString(1, nric);

			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				wonBidNum = resultSet.getInt("num_of_won_bid");
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return wonBidNum;
	}

	public boolean updateWonBid()
	{
		boolean isSuccessful = false;
		int rowAffected = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("UPDATE americano.bid SET date_won=? WHERE fk_needy_nric=? AND fk_used_item_id=?");

			preparedStatement.setTimestamp(1, new Timestamp(new Date().getTime()));
			preparedStatement.setString(2, needy.getNric());
			preparedStatement.setInt(3, usedItem.getId());

			rowAffected = preparedStatement.executeUpdate();

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if (rowAffected > 0)
			{
				isSuccessful = true;
			}
			else
			{
				isSuccessful = false;
			}

			App.mySqlConnection.close();
		}

		return isSuccessful;
	}

	public static int getTotalUniqueUsedItem()
	{

		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(DISTINCT fk_used_item_id) FROM americano.bid");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static int getTotalUniqueBidder()
	{

		int recordNum = 0;

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT COUNT(DISTINCT fk_needy_nric) FROM americano.bid");
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next())
			{
				recordNum = resultSet.getInt(1);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	public static Object[][] getTopBidder()
	{
		Object[][] data = new Object[3][3];

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT n.nric, n.name, COUNT(DISTINCT b.fk_needy_nric, b.fk_used_item_id) AS num_of_bids FROM americano.bid b INNER JOIN americano.NEEDY_VIEW n ON n.nric = b.fk_needy_nric ORDER BY num_of_bids DESC LIMIT 0, 3");
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				data[resultSet.getRow() - 1][0] = resultSet.getString("nric");
				data[resultSet.getRow() - 1][1] = resultSet.getString("name");
				data[resultSet.getRow() - 1][2] = resultSet.getInt("num_of_bids") + " bids";
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return data;
	}

	public static Object[][] getMostPopularBid()
	{
		Object[][] data = new Object[10][3];

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT DISTINCT u.type, u.title, COUNT(DISTINCT b.fk_needy_nric) AS num_of_bidders FROM americano.bid b INNER JOIN americano.used_item u ON u.id = b.fk_used_item_id ORDER BY num_of_bidders DESC LIMIT 0,10");
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				data[resultSet.getRow() - 1][0] = resultSet.getString("type");
				data[resultSet.getRow() - 1][1] = resultSet.getString("title");
				data[resultSet.getRow() - 1][2] = resultSet.getInt("num_of_bidders") + " bidders";
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return data;
	}

	public static Object[][] getRecentWonBid()
	{
		Object[][] data = new Object[10][3];

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT u.title, b.date_won, n.name FROM americano.bid b INNER JOIN americano.used_item u ON u.id = b.fk_used_item_id INNER JOIN americano.NEEDY_VIEW n on n.nric = b.fk_needy_nric WHERE b.date_won IS NOT NULL ORDER BY b.date_won DESC LIMIT 0, 10");
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				data[resultSet.getRow() - 1][0] = resultSet.getString("title");
				data[resultSet.getRow() - 1][1] = resultSet.getString("date_won");
				data[resultSet.getRow() - 1][2] = resultSet.getString("name");
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return data;
	}

	public static ArrayList<Object[]> getMultipleUniqueBidItems(int index, int count)
	{
		ArrayList<Object[]> data = new ArrayList<Object[]>();

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT u . * , n.nric, MAX( b.date_bid ) AS latest_date_bid, COUNT(DISTINCT b.fk_used_item_id, b.fk_needy_nric) AS bidders_num FROM americano.bid b INNER JOIN americano.used_item u ON b.fk_used_item_id = u.id INNER JOIN americano.needy n ON b.fk_needy_nric = n.nric WHERE u.status != 'ENDED' GROUP BY b.fk_used_item_id LIMIT ?,?");

			preparedStatement.setInt(1, index);
			preparedStatement.setInt(2, count);

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				UsedItem bidItem = new UsedItem();
				bidItem.setAttributes(resultSet);

				String nric = resultSet.getString("nric");
				Timestamp latestDateBid = resultSet.getTimestamp("latest_date_bid");
				int biddersNum = resultSet.getInt("bidders_num");

				data.add(new Object[] { bidItem, nric, latestDateBid, biddersNum });
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return data;
	}

	public static ArrayList<Bid> getMultiple(String nric, int month)
	{
		ArrayList<Bid> bids = new ArrayList<Bid>();

		try
		{
			App.mySqlConnection.open();

			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement("SELECT * FROM americano.bid WHERE fk_needy_nric = ? AND MONTH( date_bid ) = ?");
			preparedStatement.setString(1, nric);
			preparedStatement.setInt(2, month);
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				Bid bid = new Bid();
				bid.setAttributes(resultSet);
				bids.add(bid);
			}

		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			App.mySqlConnection.close();
		}

		return bids;
	}

}
